package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.EmployeeDTO;
import model.EmployeeSearchDTO;
import model.LeaveDTO;
import dao.DAOException;
import dao.EmployeeDAO;

public class EmployeeDAOImpl implements EmployeeDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveappforiss";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";

	// Create OneEmployee instance
	public EmployeeDTO createOneEmployee() {
		return new EmployeeDTO();
	}

	// Show Manager
	public ArrayList<EmployeeDTO> findAllManager() {

		ArrayList<EmployeeDTO> items = new ArrayList<EmployeeDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT e.FirstName, e.SurName, e.EmployeeID FROM employee as e WHERE e.Designation='Managers'; ";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				EmployeeDTO oneEmployee = new EmployeeDTO();				
				oneEmployee.setFirstName(rs.getString("FirstName"));
				oneEmployee.setSurName(rs.getString("SurName"));
				
				oneEmployee.setManagerID(rs.getInt("managerID"));				

				items.add(oneEmployee);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);			
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	// Find Employee by username and password
	public EmployeeDTO FindEmployeeByUserPass(String employeeEmail,
			String employeePassword) throws DAOException {
		String selectSql = "SELECT * FROM employee WHERE Email='"
				+ employeeEmail.trim() + "' AND Password = '"
				+ employeePassword.trim() + "';";
		EmployeeDTO oneEmployee = loadEmployee(selectSql);
		System.out.println(oneEmployee.toString());
		return oneEmployee;
	}

	// Find Employee by id
	public EmployeeDTO findEmployee(int employeeID) throws DAOException {
		String selectSql = "SELECT * FROM employee WHERE EmployeeID='"
				+ employeeID + "';";
		EmployeeDTO oneEmployee = loadEmployee(selectSql);
		System.out.println(oneEmployee.toString());
		return oneEmployee;
	}

	public EmployeeDTO findEmployeewithfname(String firstName)
			throws DAOException {
		String selectSql = "SELECT * FROM employee WHERE FirstName='"
				+ firstName.trim() + "';";
		EmployeeDTO oneEmployee = loadEmployee(selectSql);
		System.out.println(oneEmployee.toString());
		return oneEmployee;
	}

	public EmployeeDTO findEmployee(String employeeName) throws DAOException {
		String selectSql = "SELECT * FROM employee WHERE FirstName='"
				+ employeeName.trim() + "';";
		EmployeeDTO oneEmployee = loadEmployee(selectSql);
		System.out.println(oneEmployee.toString());
		return oneEmployee;
	}

	private EmployeeDTO loadEmployee(String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		EmployeeDTO oneEmployee = createOneEmployee();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				oneEmployee.setEmployeeID(result.getInt("EmployeeID"));
				oneEmployee.setFirstName(result.getString("FirstName"));
				oneEmployee.setSurName(result.getString("SurName"));
				oneEmployee.setDesignation(result.getString("Designation"));
				oneEmployee.setRole(result.getString("Role"));
				oneEmployee.setTelNumber(result.getString("Telephone"));
				oneEmployee.setAddress(result.getString("Address"));
				oneEmployee.setEmail(result.getString("Email"));
				oneEmployee.setPassword(result.getString("Password"));
				oneEmployee.setManagerID(result.getInt("ManagerID"));
				oneEmployee.setGender(result.getString("Gender"));
				oneEmployee.setNric(result.getString("NRIC"));

			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return oneEmployee;
	}

	public ArrayList<EmployeeDTO> findEmployeeByManager(int managerID,
			String status) throws DAOException {

		ArrayList<EmployeeDTO> items = new ArrayList<EmployeeDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT e.*,l.*,lt.* FROM employee as e,employee as m,leaves as l,leavetype as lt WHERE m.EmployeeID=e.ManagerID AND e.EmployeeID=l.EmployeeID AND m.EmployeeID =' "
				+ managerID
				+ "'AND lt.leavetypeid=l.leavetypeid AND l.Status = '"
				+ status
				+ "';";
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				EmployeeDTO oneEmployee = new EmployeeDTO();
				// oneEmployee.setEmployeeID(rs.getInt("employeeID"));
				oneEmployee.setFirstName(rs.getString("FirstName"));
				oneEmployee.setSurName(rs.getString("SurName"));
				oneEmployee.setDesignation(rs.getString("Designation"));
				oneEmployee.setRole(rs.getString("Role"));
				oneEmployee.setTelNumber(rs.getString("telephone"));
				oneEmployee.setAddress(rs.getString("address"));
				oneEmployee.setEmail(rs.getString("email"));
				oneEmployee.setPassword(rs.getString("password"));
				oneEmployee.setManagerID(rs.getInt("managerID"));
				oneEmployee.setGender(rs.getString("gender"));
				oneEmployee.setNric(rs.getString("nric"));

				oneEmployee.setName(rs.getString("name"));

				oneEmployee.setStartDate(rs.getDate("startDate"));
				oneEmployee.setEndDate(rs.getDate("endDate"));
				oneEmployee.setPeriod(rs.getDouble("leaveperiod"));
				oneEmployee.setReason(rs.getString("reason"));
				oneEmployee.setWorkDissmenition(rs.getInt("workDissmenition"));
				oneEmployee.setStatus(rs.getString("status"));
				oneEmployee.setComment(rs.getString("comment"));
				oneEmployee.setCreateBy(rs.getInt("createBy"));
				oneEmployee.setCreateDate(rs.getDate("createDate"));

				items.add(oneEmployee);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public ArrayList<EmployeeDTO> findAllEmployee() throws DAOException {

		String selectSql = "SELECT * FROM employee ORDER BY EmployeeID ASC;";
		ArrayList<EmployeeDTO> items = loadAllEmployee(selectSql);
		return items;
	}

	private ArrayList<EmployeeDTO> loadAllEmployee(String selectSql)
			throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		ArrayList<EmployeeDTO> items = new ArrayList<EmployeeDTO>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				EmployeeDTO oneEmployee = createOneEmployee();
				oneEmployee.setEmployeeID(result.getInt("EmployeeID"));
				oneEmployee.setFirstName(result.getString("FirstName"));
				oneEmployee.setSurName(result.getString("SurName"));
				oneEmployee.setDesignation(result.getString("Designation"));
				oneEmployee.setRole(result.getString("Role"));
				oneEmployee.setTelNumber(result.getString("Telephone"));
				oneEmployee.setAddress(result.getString("Address"));
				oneEmployee.setEmail(result.getString("Email"));
				oneEmployee.setPassword(result.getString("Password"));
				oneEmployee.setManagerID(result.getInt("ManagerID"));
				oneEmployee.setGender(result.getString("Gender"));
				oneEmployee.setNric(result.getString("NRIC"));
				items.add(oneEmployee);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public void addEmployee(EmployeeDTO oneEmployee) throws SQLException {
		String insertSql = "INSERT INTO employee VALUES('"
				+ oneEmployee.getEmployeeID() + "', '"
				+ oneEmployee.getFirstName() + "', '"
				+ oneEmployee.getSurName() + "', '"
				+ oneEmployee.getDesignation() + "', '" + oneEmployee.getRole()
				+ "', '" + oneEmployee.getTelNumber() + "', '"
				+ oneEmployee.getAddress() + "', '" + oneEmployee.getEmail()
				+ "', '" + oneEmployee.getPassword() + "', '"
				+ oneEmployee.getManagerID() + "', '" + oneEmployee.getGender()
				+ "', '" + oneEmployee.getNric() + "');";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing insert: " + insertSql);
			int res = st.executeUpdate(insertSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new SQLException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	/*
	 * private int getNewEmployeeID() throws SQLException { int id =
	 * countAllEmployee(); id++; return id; }
	 */
	public void updateEmployee(EmployeeDTO oneEmployee) throws DAOException,
			SQLException {
		String updateSql = "UPDATE employee SET SurName = '"
				+ oneEmployee.getSurName() + "', Designation = '"
				+ oneEmployee.getDesignation() + "', Role = '"
				+ oneEmployee.getRole() + "', Telephone = '"
				+ oneEmployee.getTelNumber() + "', Address = '"
				+ oneEmployee.getAddress() + "', Email = '"
				+ oneEmployee.getEmail() + "', Password = '"
				+ oneEmployee.getPassword() + "', ManagerID = '"
				+ oneEmployee.getManagerID() + "', Gender = '"
				+ oneEmployee.getGender() + "', NRIC = '"
				+ oneEmployee.getNric() + "' WHERE FirstName='"
				+ oneEmployee.getFirstName() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
	}

	/*
	 * public void addEmployee(EmployeeDTO oneEmployee) throws SQLException {
	 * 
	 * String sql = ""; Connection conn = null; PreparedStatement stmt = null;
	 * ResultSet result = null;
	 * 
	 * try { sql = "INSERT INTO employee ( EmployeeID, FirstName, SurName, " +
	 * "Designation,Role, Telephone, Address, " + "Email, Password, ManagerID, "
	 * + "Gender, NRIC) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "; conn =
	 * DriverManager.getConnection(dbUrl, dbUserName, dbPassword); stmt =
	 * conn.prepareStatement(sql);
	 * 
	 * stmt.setInt(1, getNewEmployeeID()); // calculate next id
	 * stmt.setString(2, oneEmployee.getFirstName()); stmt.setString(3,
	 * oneEmployee.getSurName()); stmt.setString(4,
	 * oneEmployee.getDesignation()); stmt.setString(5, oneEmployee.getRole());
	 * stmt.setString(6, oneEmployee.getTelNumber()); stmt.setString(7,
	 * oneEmployee.getAddress()); stmt.setString(8, oneEmployee.getEmail());
	 * stmt.setString(9, oneEmployee.getPassword()); stmt.setInt(10,
	 * oneEmployee.getManagerID()); stmt.setString(11, oneEmployee.getGender());
	 * stmt.setString(12, oneEmployee.getNric());
	 * 
	 * int rowcount = databaseUpdate(conn, stmt); if (rowcount != 1) {
	 * //System.out.println("PrimaryKey Error when updating DB!"); throw new
	 * SQLException("PrimaryKey Error when updating DB!"); }
	 * 
	 * } finally { if (stmt != null) stmt.close(); } }
	 */

	/*
	 * public void updateEmployee(EmployeeDTO oneEmployee) throws DAOException,
	 * SQLException {
	 * 
	 * try { Class.forName("com.mysql.jdbc.Driver"); } catch
	 * (ClassNotFoundException e1) { // TODO Auto-generated catch block
	 * e1.printStackTrace(); } String sql =
	 * "UPDATE employee SET FirstName = ?, SurName = ?, Designation = ?, Role = ?, "
	 * + "Telephone = ?, Address = ?, Email = ?, " +
	 * "Password = ?, ManagerID = ?, Gender = ?, " +
	 * "NRIC = ? WHERE (FirstName = ? ) "; PreparedStatement stmt = null;
	 * Connection conn = DriverManager.getConnection(dbUrl, dbUserName,
	 * dbPassword);
	 * 
	 * try { stmt = conn.prepareStatement(sql); stmt.setString(1,
	 * oneEmployee.getFirstName()); stmt.setString(2, oneEmployee.getSurName());
	 * stmt.setString(3, oneEmployee.getDesignation()); stmt.setString(4,
	 * oneEmployee.getRole()); stmt.setString(5, oneEmployee.getTelNumber());
	 * stmt.setString(6, oneEmployee.getAddress()); stmt.setString(7,
	 * oneEmployee.getEmail()); stmt.setString(8, oneEmployee.getPassword());
	 * stmt.setInt(9, oneEmployee.getManagerID()); stmt.setString(10,
	 * oneEmployee.getGender()); stmt.setString(11, oneEmployee.getNric());
	 * 
	 * //stmt.setInt(12, oneEmployee.getEmployeeID());
	 * 
	 * int rowcount = databaseUpdate(conn, stmt); if (rowcount == 0) { //
	 * System.out.println("Object could not be saved! (PrimaryKey not found)");
	 * throw new DAOException(
	 * "Object could not be saved! (PrimaryKey not found)"); } if (rowcount > 1)
	 * { // System.out.println(
	 * "PrimaryKey Error when updating DB! (Many objects were affected!)");
	 * throw new SQLException(
	 * "PrimaryKey Error when updating DB! (Many objects were affected!)"); } }
	 * finally { if (stmt != null) stmt.close(); } }
	 */
	public void deleteEmployee(EmployeeDTO oneEmployee) throws DAOException,
			SQLException {

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String sql = "DELETE FROM employee WHERE (employeeID = ? ) ";
		PreparedStatement stmt = null;

		try {
			Connection conn = DriverManager.getConnection(dbUrl, dbUserName,
					dbPassword);
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, oneEmployee.getEmployeeID());

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new DAOException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void deleteAllEmployee() throws SQLException {

		String sql = "DELETE FROM employee";
		PreparedStatement stmt = null;

		try {
			Connection conn = DriverManager.getConnection(dbUrl, dbUserName,
					dbPassword);
			stmt = conn.prepareStatement(sql);
			int rowcount = databaseUpdate(conn, stmt);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public int countAllEmployee() throws SQLException {

		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		String sql = "SELECT count(*) FROM employee";
		PreparedStatement stmt = null;
		ResultSet result = null;
		int allRows = 0;
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			stmt = conn.prepareStatement(sql);
			result = stmt.executeQuery();

			if (result.next())
				allRows = result.getInt(1);
		} finally {
			try {
				if (result != null)
					result.close();
				if (stmt != null)
					stmt.close();
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return allRows;
	}

	/*
	 * public ArrayList<EmployeeDTO> searchMatching(EmployeeDTO oneEmployee)
	 * throws SQLException {
	 * 
	 * ArrayList<EmployeeDTO> searchResults;
	 * 
	 * boolean first = true; StringBuffer sql = new
	 * StringBuffer("SELECT * FROM Employee WHERE 1=1 ");
	 * 
	 * if (oneEmployee.getEmployeeID() != 0) { if (first) { first = false; }
	 * sql.
	 * append("AND employeeID = ").append(oneEmployee.getEmployeeID()).append
	 * (" "); }
	 * 
	 * if (oneEmployee.getFirstName() != null) { if (first) { first = false; }
	 * sql
	 * .append("AND FirstName LIKE '").append(oneEmployee.getFirstName()).append
	 * ("%' "); }
	 * 
	 * if (oneEmployee.getSurName() != null) { if (first) { first = false; }
	 * sql.
	 * append("AND SurName LIKE '").append(oneEmployee.getSurName()).append(
	 * "%' " ); } if (oneEmployee.getDesignation() != null) { if (first) { first
	 * = false; }
	 * sql.append("AND Designation LIKE '").append(oneEmployee.getDesignation
	 * ()).append("%' "); }
	 * 
	 * if (oneEmployee.getTelNumber() != null) { if (first) { first = false; }
	 * sql
	 * .append("AND Tel LIKE '").append(oneEmployee.getTelNumber()).append("%' "
	 * ); }
	 * 
	 * if (oneEmployee.getAddress() != null) { if (first) { first = false; }
	 * sql.
	 * append("AND Address LIKE '").append(oneEmployee.getAddress()).append(
	 * "%' " ); }
	 * 
	 * if (oneEmployee.getEmail() != null) { if (first) { first = false; }
	 * sql.append
	 * ("AND Email LIKE '").append(oneEmployee.getEmail()).append("%' "); }
	 * 
	 * if (oneEmployee.getPassword() != null) { if (first) { first = false; }
	 * sql
	 * .append("AND Password LIKE '").append(oneEmployee.getPassword()).append
	 * ("%' "); }
	 * 
	 * if (oneEmployee.getManagerID() != 0) { if (first) { first = false; }
	 * sql.append
	 * ("AND ManagerID = ").append(oneEmployee.getManagerID()).append(" "); }
	 * 
	 * if (oneEmployee.getGender() != null) { if (first) { first = false; }
	 * sql.append
	 * ("AND Gender LIKE '").append(oneEmployee.getGender()).append("%' "); }
	 * 
	 * if (oneEmployee.getNric() != null) { if (first) { first = false; }
	 * sql.append
	 * ("AND NRIC LIKE '").append(oneEmployee.getNric()).append("%' "); }
	 * 
	 * 
	 * sql.append("ORDER BY employeeID ASC ");
	 * 
	 * // Prevent accidential full table results. // Use loadAll if all rows
	 * must be returned. if (first) searchResults = new ArrayList(); else
	 * searchResults = listQuery(conn, conn.prepareStatement(sql.toString()));
	 * 
	 * return searchResults; }
	 */

	protected int databaseUpdate(Connection conn, PreparedStatement stmt)
			throws SQLException {

		int result = stmt.executeUpdate();

		return result;
	}

	public ArrayList<EmployeeDTO> findEmployeeByCriteria(EmployeeSearchDTO so)
			throws DAOException {

		String selectSql = null;
		if (so.getName().trim().equalsIgnoreCase("")) {

			selectSql = "SELECT * FROM employee WHERE Name LIKE '"
					+ so.getName().trim() + "%';";
		} else {
			if (so.getSurName().trim().equalsIgnoreCase("")) {
				selectSql = "SELECT * FROM employee WHERE SurName LIKE '"
						+ so.getSurName().trim() + "%';";
			} else {
				selectSql = "SELECT * FROM employee WHERE Name LIKE '"
						+ so.getName().trim() + "%' AND SurName LIKE '"
						+ so.getSurName() + "%';";
			}
		}
		ArrayList<EmployeeDTO> list = loadAllEmployee(selectSql);
		return list;
	}
}
